package Classes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Vector;

/**
 * 
 * @author Karla Barber
 * General Booking Class creation
 *
 */
public class Booking 
{
	//===============================Properties=======================================
	// start Karla Barber properties
	private int bookingId;
	private Date bookingDate;
	private String bookingNo;
	private double travelerCount;
	private String tripTypeId;
	private String packageId;
	private int customerId;
	private String packageName;
	
	// end Karla Barber properties
	
	//===============================Constructors=======================================
	
	//===============================Getters and Setters================================
	// start Karla Barber getters and setters
	
	public int getBookingId() {
		return bookingId;
	}

	public void setBookingId(int bookingId) {
		this.bookingId = bookingId;
	}

	public Date getBookingDate() {
		return bookingDate;
	}

	public void setBookingDate(Date bookingDate) {
		this.bookingDate = bookingDate;
	}

	public String getBookingNo() {
		return bookingNo;
	}

	public void setBookingNo(String bookingNo) {
		this.bookingNo = bookingNo;
	}

	public double getTravelerCount() {
		return travelerCount;
	}

	public void setTravelerCount(double travelerCount) {
		this.travelerCount = travelerCount;
	}

	public String getTripTypeId() {
		return tripTypeId;
	}

	public void setTripTypeId(String tripTypeId) {
		this.tripTypeId = tripTypeId;
	}

	public String getPackageId() {
		return packageId;
	}

	public void setPackageId(String packageId) {
		this.packageId = packageId;
	}

	public int getCustomerId() {
		return customerId;
	}

	public void setCustomerId(int customerId) {
		this.customerId = customerId;
	}

	public void setPackageName(String packageName) {
		this.packageName = packageName;
	}

	public String getPackageName() {
		return packageName;
	}
	// end Karla Barber getters and setters
	
	//===============================Methods============================================
	@Override
	public String toString() {
		return bookingNo + " - " + bookingDate + " - " + packageName;
	}

    //this method connects to the DB
    public Connection connectToDb()
    {
            try 
            {       
                    //load the driver for oracle
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    
                    //establish a connection to the database
                    //first string is location of the database (url)
                    //second string is user name
                    //third string is password
                    Connection agentConnection =  DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g", "ictoosd", "ictoosd");
                    return agentConnection;
            } 
            catch (ClassNotFoundException e) 
            {
                    e.printStackTrace();
                    Connection noConnectionMade = null;
                    return noConnectionMade;
            } catch (SQLException sqle) 
            {
                    sqle.printStackTrace();
                    Connection noConnectionMade = null;
                    return noConnectionMade;
            }       
    }
    
    //this method receives a sql statement, runs it and process the result set into a vector
    public Vector<String> loadBookingVector(String sql) 
    {
            Vector v;
            v = new Vector();
            try 
            {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
                    Statement stmt = dbConn.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next())
                    {
                            Booking b = new Booking();
                            b.setBookingId(rs.getInt("BOOKINGID"));
                            b.setBookingDate(rs.getDate("BOOKINGDATE"));
                            b.setBookingNo(rs.getString("BOOKINGNO"));
                            b.setTravelerCount(rs.getDouble("TRAVELERCOUNT"));
                            b.setTripTypeId(rs.getString("TRIPTYPEID"));
                            b.setPackageId(rs.getString("PACKAGEID"));
                            b.setCustomerId(rs.getInt("CUSTOMERID"));
                            b.setPackageName(rs.getString("PKGNAME"));
                            v.addElement(b);
                    }
                    dbConn.close();
            } 
            catch (SQLException e) 
            {
                    e.printStackTrace();
            } catch (ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return v;
    }
    
    //This method receives a customer id, finds all bookings for the customer
    //determines if there is still money owing on the booking and if so,
    //returns it in a vector
    public Vector<String> loadOSBookings(int CId)
    {
    	double totCost;
    	double totPmt;
        Vector v;
        v = new Vector();
    	try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
            Statement stmt1 = dbConn.createStatement();
            String lsql1 = "SELECT SUM(BASEPRICE), B.*, P.PKGNAME " +
            			   "FROM ICTOOSD.BOOKINGDETAILS BD, ICTOOSD.BOOKINGS B, ICTOOSD.PACKAGES P " + 	
            			   "WHERE B.CUSTOMERID = " + CId + " " +
            			   "AND BD.BOOKINGID = B.BOOKINGID " + 
            			   "AND B.PACKAGEID = P.PACKAGEID " +
            			   "GROUP BY B.BOOKINGID, B.BOOKINGDATE, B.BOOKINGNO, B.TRAVELERCOUNT, " +
            			   "B.CUSTOMERID, B.TRIPTYPEID, B.PACKAGEID, P.PKGNAME";
            
            ResultSet rs1 = stmt1.executeQuery(lsql1);
            while (rs1.next())
            {
            	totCost = rs1.getDouble(1);
            	String lsql2 = "SELECT SUM(PMT_AMT) TOTPMT FROM ICTOOSD.PAYMENTS " +
				  			   "WHERE BOOKINGID = " + rs1.getInt("BOOKINGID") + " " +
				  			   "GROUP BY BOOKINGID";
            	
                Statement stmt2 = dbConn.createStatement();
            	ResultSet rs2 = stmt2.executeQuery(lsql2);
            	
            	if (rs2.next())
            	{
            		totPmt = rs2.getDouble(1);
	            	if (totCost > totPmt)
	            	{
	            		Booking b = new Booking();
	                    b.setBookingId(rs1.getInt("BOOKINGID"));
	                    b.setBookingDate(rs1.getDate("BOOKINGDATE"));
	                    b.setBookingNo(rs1.getString("BOOKINGNO"));
	                    b.setTravelerCount(rs1.getDouble("TRAVELERCOUNT"));
	                    b.setTripTypeId(rs1.getString("TRIPTYPEID"));
	                    b.setPackageId(rs1.getString("PACKAGEID"));
	                    b.setCustomerId(rs1.getInt("CUSTOMERID"));
	                    b.setPackageName(rs1.getString("PKGNAME"));
	                    v.addElement(b);
	            	}
            	}
            	else
            	{
            		Booking b = new Booking();
                    b.setBookingId(rs1.getInt("BOOKINGID"));
                    b.setBookingDate(rs1.getDate("BOOKINGDATE"));
                    b.setBookingNo(rs1.getString("BOOKINGNO"));
                    b.setTravelerCount(rs1.getDouble("TRAVELERCOUNT"));
                    b.setTripTypeId(rs1.getString("TRIPTYPEID"));
                    b.setPackageId(rs1.getString("PACKAGEID"));
                    b.setCustomerId(rs1.getInt("CUSTOMERID"));
                    b.setPackageName(rs1.getString("PKGNAME"));
                    v.addElement(b);
            	}
            }
            dbConn.close();
    } 
    catch (SQLException e) 
    {
            e.printStackTrace();
    } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
    }
    	return v;
    }
}
